import logging

from pylons import request, response, session, tmpl_context as c
from pylons.controllers.util import abort, redirect_to

from cpswarehouse.lib.base import BaseController, render

from cpswarehouse.model import SchoolDimension, RecordFact
from cpswarehouse.model import record_fact, school_dimension, race_dimension, year_dimension
from cpswarehouse.model import meta
from cpswarehouse.model.meta import Session

log = logging.getLogger(__name__)

from sqlalchemy.sql import select
from sqlalchemy import func

from cpswarehouse.lib.starquery import CpsStarQuery as StarQuery
from cpswarehouse.lib.charts import RacePieChart

from collections import defaultdict


from pylons.decorators.cache import beaker_cache

class SchoolsController(BaseController):

    @beaker_cache()
    def index(self):
        schools = Session.query(SchoolDimension).order_by(SchoolDimension.school)

        letters_to_schools = defaultdict(list)
        
        for school in schools:
            try:
                first_letter = school.school[0]
            except IndexError:
                continue
            letters_to_schools[first_letter].append(school)

        letters = letters_to_schools.keys()
        letters.sort()

        c.letters = letters
        c.schools = letters_to_schools
        
        return render('/schools/index.mako')

    @beaker_cache()
    def detail(self, school):
        school_obj = Session.query(SchoolDimension).filter(SchoolDimension.school == school).one()
        c.school = school_obj

        starquery = StarQuery()
        years = starquery.select_race(func=func.sum).\
            select_year().\
            select_school().\
            where_school_is(school_obj.id).\
            group_by_year().\
            fetch_all()

        #years = list(years)
        c.year_query = list(years)

        return render('/schools/detail.mako')

    @beaker_cache()    
    def detail_with_year(self, school, year):
        school_obj = Session.query(SchoolDimension).filter(SchoolDimension.school == school).one()
        c.school = school_obj

        starquery = StarQuery()
        years = starquery.select_race(func=func.sum).\
            select_year().\
            select_school().\
            where_school_is(school_obj.id).\
            where_year_is(year).\
            group_by_year().\
            fetch_all()
        years = list(years)
        assert(len(years) == 1)
        c.year = years[0]

        pie_chart = RacePieChart(asian = c.year.asian,
                                 black = c.year.black,
                                 hispanic = c.year.hispanic,
                                 nativeamerican = c.year.nativeamerican,
                                 white = c.year.white)

        c.pie_chart = pie_chart

        starquery = StarQuery()
        grades = starquery.select_race().\
            select_year().\
            select_grade().\
            select_school().\
            where_school_is(school_obj.id).\
            where_year_is(year).\
            fetch_all()
        c.grades = list(grades)

        return render('/schools/detail_one_year.mako')       
